{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Guest House - Easy\n",
    "\n",
    "Data for this assessment is available:\n",
    "\n",
    "- [guesthouse data in MySQL format](http://sqlzoo.net/guesthouse.sql)\n",
    "- [guesthouse data in Microsoft SQL Server format](http://sqlzoo.net/guesthouse-ms.sql)\n",
    "\n",
    "Background\n",
    "\n",
    "- Guests stay at a small hotel.\n",
    "- Each booking is recorded in the table booking, the date of the first night of the booking is stored here (we do not record the date the booking was made)\n",
    "- At the time of booking the room to be used is decided\n",
    "- There are several room types (single, double..)\n",
    "- The amount charged depends on the room type and the number of people staying and the number of nights\n",
    "- Guests may be charged extras (for breakfast or using the minibar)\n",
    "- **Database Description** | [Easy Problems](https://sqlzoo.net/wiki/Guest_House_Assessment_Easy) | [Medium Problems](https://sqlzoo.net/wiki/Guest_House_Assessment_Medium) | [Hard Problems](https://sqlzoo.net/wiki/Guest_House_Assessment_Hard)\n",
    "- [Guest House Assessment Sample Queries](https://sqlzoo.net/wiki/Guest_House_Assessment_Sample_Queries)\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/8/83/Hotel.png)\n",
    "\n",
    "## Table booking\n",
    "The table booking contains an entry for every booking made at the hotel. A booking is made by one guest - even though more than one person may be staying we do not record the details of other guests in the same room. In normal operation the table includes both past and future bookings.\n",
    "\n",
    "```\n",
    "+----------+------------+-------+--------+---------+-------------------+------+------------+\n",
    "|booking_id|booking_date|room_no|guest_id|occupants|room_type_requested|nights|arrival_time|\n",
    "+----------+------------+-------+--------+---------+-------------------+------+------------+\n",
    "|     5001 | 2016-11-03 |   101 |   1027 |       1 | single            |    7 | 13:00      |\n",
    "|     5002 | 2016-11-03 |   102 |   1179 |       1 | double            |    2 | 18:00      |\n",
    "|     5003 | 2016-11-03 |   103 |   1106 |       2 | double            |    2 | 21:00      |\n",
    "|     5004 | 2016-11-03 |   104 |   1238 |       1 | double            |    3 | 22:00      |\n",
    "+----------+------------+-------+--------+---------+-------------------+------+------------+\n",
    "```\n",
    "\n",
    "## Table room\n",
    "Rooms are either single, double, twin or family.\n",
    "\n",
    "```\n",
    "+-----+-----------+---------------+\n",
    "| id  | room_type | max_occupancy |\n",
    "+-----+-----------+---------------+\n",
    "| 101 | single    |             1 |\n",
    "| 102 | double    |             2 |\n",
    "| 103 | double    |             2 |\n",
    "| 104 | double    |             2 |\n",
    "| 105 | family    |             3 |\n",
    "+-----+-----------+---------------+\n",
    "```\n",
    "\n",
    "## Table rate\n",
    "Rooms are charged per night, the amount charged depends on the \"room type requested\" value of the booking and the number of people staying:\n",
    "\n",
    "```\n",
    "+-----------+-----------+--------+\n",
    "| room_type | occupancy | amount |\n",
    "+-----------+-----------+--------+\n",
    "| double    |         1 |  56.00 |\n",
    "| double    |         2 |  72.00 |\n",
    "| family    |         1 |  56.00 |\n",
    "| family    |         2 |  72.00 |\n",
    "| family    |         3 |  84.00 |\n",
    "| single    |         1 |  48.00 |\n",
    "| twin      |         1 |  50.00 |\n",
    "| twin      |         2 |  72.00 |\n",
    "+-----------+-----------+--------+\n",
    "```\n",
    "\n",
    "You can see that a double room with one person staying costs £56 while a double room with 2 people staying costs £72 per night\n",
    "\n",
    "Note that the actual room assigned to the booking might not match the room required (a customer may ask for a single room but we actually assign her a double). In this case we charge at the \"requirement rate\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "── \u001b[1mAttaching packages\u001b[22m ─────────────────────────────────────── tidyverse 1.3.0 ──\n",
      "\n",
      "\u001b[32m✔\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32m✔\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32m✔\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32m✔\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32m✔\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.3     \u001b[32m✔\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32m✔\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32m✔\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "── \u001b[1mConflicts\u001b[22m ────────────────────────────────────────── tidyverse_conflicts() ──\n",
      "\u001b[31m✖\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31m✖\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ····\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "Guest 1183. Give the booking_date and the number of nights for guest 1183.\n",
    "\n",
    "```\n",
    "+--------------+--------+\n",
    "| booking_date | nights |\n",
    "+--------------+--------+\n",
    "| 2016-11-27   |      5 |\n",
    "+--------------+--------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "rate <- dbReadTable(con, 'rate')\n",
    "extra <- dbReadTable(con, 'extra')\n",
    "booking <- dbReadTable(con, 'booking')\n",
    "room <- dbReadTable(con, 'room')\n",
    "room_type <- dbReadTable(con, 'room_type')\n",
    "guest <- dbReadTable(con, 'guest')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>booking_date</th><th scope=col>nights</th></tr>\n",
       "\t<tr><th scope=col>&lt;date&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2016-11-27</td><td>5</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 2\n",
       "\\begin{tabular}{ll}\n",
       " booking\\_date & nights\\\\\n",
       " <date> & <int>\\\\\n",
       "\\hline\n",
       "\t 2016-11-27 & 5\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 2\n",
       "\n",
       "| booking_date &lt;date&gt; | nights &lt;int&gt; |\n",
       "|---|---|\n",
       "| 2016-11-27 | 5 |\n",
       "\n"
      ],
      "text/plain": [
       "  booking_date nights\n",
       "1 2016-11-27   5     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "booking %>%\n",
    "    filter(booking_date==as.Date('2016-11-27') &\n",
    "           guest_id==1183) %>%\n",
    "    select(booking_date, nights)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "When do they get here? List the arrival time and the first and last names for all guests due to arrive on 2016-11-05, order the output by time of arrival.\n",
    "\n",
    "```\n",
    "+--------------+------------+-----------+\n",
    "| arrival_time | first_name | last_name |\n",
    "+--------------+------------+-----------+\n",
    "| 14:00        | Lisa       | Nandy     |\n",
    "| 15:00        | Jack       | Dromey    |\n",
    "| 16:00        | Mr Andrew  | Tyrie     |\n",
    "| 21:00        | James      | Heappey   |\n",
    "| 22:00        | Justin     | Tomlinson |\n",
    "+--------------+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 5 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>arrival_time</th><th scope=col>first_name</th><th scope=col>last_name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>14:00</td><td>Lisa     </td><td>Nandy    </td></tr>\n",
       "\t<tr><td>15:00</td><td>Jack     </td><td>Dromey   </td></tr>\n",
       "\t<tr><td>16:00</td><td>Mr Andrew</td><td>Tyrie    </td></tr>\n",
       "\t<tr><td>21:00</td><td>James    </td><td>Heappey  </td></tr>\n",
       "\t<tr><td>22:00</td><td>Justin   </td><td>Tomlinson</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 5 × 3\n",
       "\\begin{tabular}{lll}\n",
       " arrival\\_time & first\\_name & last\\_name\\\\\n",
       " <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 14:00 & Lisa      & Nandy    \\\\\n",
       "\t 15:00 & Jack      & Dromey   \\\\\n",
       "\t 16:00 & Mr Andrew & Tyrie    \\\\\n",
       "\t 21:00 & James     & Heappey  \\\\\n",
       "\t 22:00 & Justin    & Tomlinson\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 5 × 3\n",
       "\n",
       "| arrival_time &lt;chr&gt; | first_name &lt;chr&gt; | last_name &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 14:00 | Lisa      | Nandy     |\n",
       "| 15:00 | Jack      | Dromey    |\n",
       "| 16:00 | Mr Andrew | Tyrie     |\n",
       "| 21:00 | James     | Heappey   |\n",
       "| 22:00 | Justin    | Tomlinson |\n",
       "\n"
      ],
      "text/plain": [
       "  arrival_time first_name last_name\n",
       "1 14:00        Lisa       Nandy    \n",
       "2 15:00        Jack       Dromey   \n",
       "3 16:00        Mr Andrew  Tyrie    \n",
       "4 21:00        James      Heappey  \n",
       "5 22:00        Justin     Tomlinson"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "booking %>%\n",
    "    filter(as.Date(booking_date)==as.Date('2016-11-05')) %>%\n",
    "    inner_join(guest, by=c(guest_id=\"id\")) %>%\n",
    "    select(arrival_time, first_name, last_name) %>%\n",
    "    arrange(arrival_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Look up daily rates. Give the daily rate that should be paid for bookings with ids 5152, 5165, 5154 and 5295. Include booking id, room type, number of occupants and the amount.\n",
    "\n",
    "```\n",
    "+------------+---------------------+-----------+--------+\n",
    "| booking_id | room_type_requested | occupants | amount |\n",
    "+------------+---------------------+-----------+--------+\n",
    "|       5152 | double              |         2 |  72.00 |\n",
    "|       5154 | double              |         1 |  56.00 |\n",
    "|       5295 | family              |         3 |  84.00 |\n",
    "+------------+---------------------+-----------+--------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>booking_id</th><th scope=col>room_type_requested</th><th scope=col>occupants</th><th scope=col>amount</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>5152</td><td>double</td><td>2</td><td>72</td></tr>\n",
       "\t<tr><td>5154</td><td>double</td><td>1</td><td>56</td></tr>\n",
       "\t<tr><td>5295</td><td>family</td><td>3</td><td>84</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 4\n",
       "\\begin{tabular}{llll}\n",
       " booking\\_id & room\\_type\\_requested & occupants & amount\\\\\n",
       " <int> & <chr> & <int> & <dbl>\\\\\n",
       "\\hline\n",
       "\t 5152 & double & 2 & 72\\\\\n",
       "\t 5154 & double & 1 & 56\\\\\n",
       "\t 5295 & family & 3 & 84\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 4\n",
       "\n",
       "| booking_id &lt;int&gt; | room_type_requested &lt;chr&gt; | occupants &lt;int&gt; | amount &lt;dbl&gt; |\n",
       "|---|---|---|---|\n",
       "| 5152 | double | 2 | 72 |\n",
       "| 5154 | double | 1 | 56 |\n",
       "| 5295 | family | 3 | 84 |\n",
       "\n"
      ],
      "text/plain": [
       "  booking_id room_type_requested occupants amount\n",
       "1 5152       double              2         72    \n",
       "2 5154       double              1         56    \n",
       "3 5295       family              3         84    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "booking %>%\n",
    "    filter(booking_id %in% c(5152, 6165, 5154, 5295)) %>%\n",
    "    inner_join(room, by=c(room_no=\"id\")) %>%\n",
    "    inner_join(rate, by=c(room_type=\"room_type\", occupants=\"occupancy\")) %>%\n",
    "    select(booking_id, room_type_requested, occupants, amount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "Who’s in 101? Find who is staying in room 101 on 2016-12-03, include first name, last name and address.\n",
    "\n",
    "```\n",
    "+------------+-----------+-------------+\n",
    "| first_name | last_name | address     |\n",
    "+------------+-----------+-------------+\n",
    "| Graham     | Evans     | Weaver Vale |\n",
    "+------------+-----------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>first_name</th><th scope=col>last_name</th><th scope=col>address</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Graham</td><td>Evans</td><td>Weaver Vale</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 3\n",
       "\\begin{tabular}{lll}\n",
       " first\\_name & last\\_name & address\\\\\n",
       " <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Graham & Evans & Weaver Vale\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 3\n",
       "\n",
       "| first_name &lt;chr&gt; | last_name &lt;chr&gt; | address &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| Graham | Evans | Weaver Vale |\n",
       "\n"
      ],
      "text/plain": [
       "  first_name last_name address    \n",
       "1 Graham     Evans     Weaver Vale"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "booking %>%\n",
    "    filter(room_no==101 &\n",
    "           booking_date <= as.Date('2016-12-03') + nights &\n",
    "           booking_date >= as.Date('2016-12-03')) %>%\n",
    "    inner_join(guest, by=c(guest_id=\"id\")) %>%\n",
    "    select(first_name, last_name, address)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "How many bookings, how many nights? For guests 1185 and 1270 show the number of bookings made and the total number of nights. Your output should include the guest id and the total number of bookings and the total number of nights.\n",
    "\n",
    "```\n",
    "+----------+---------------+-------------+\n",
    "| guest_id | COUNT(nights) | SUM(nights) |\n",
    "+----------+---------------+-------------+\n",
    "|     1185 |             3 |           8 |\n",
    "|     1270 |             2 |           3 |\n",
    "+----------+---------------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 2 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>guest_id</th><th scope=col>n_booking</th><th scope=col>n_nights</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1185</td><td>3</td><td>8</td></tr>\n",
       "\t<tr><td>1270</td><td>2</td><td>3</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 2 × 3\n",
       "\\begin{tabular}{lll}\n",
       " guest\\_id & n\\_booking & n\\_nights\\\\\n",
       " <int> & <int> & <int>\\\\\n",
       "\\hline\n",
       "\t 1185 & 3 & 8\\\\\n",
       "\t 1270 & 2 & 3\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 2 × 3\n",
       "\n",
       "| guest_id &lt;int&gt; | n_booking &lt;int&gt; | n_nights &lt;int&gt; |\n",
       "|---|---|---|\n",
       "| 1185 | 3 | 8 |\n",
       "| 1270 | 2 | 3 |\n",
       "\n"
      ],
      "text/plain": [
       "  guest_id n_booking n_nights\n",
       "1 1185     3         8       \n",
       "2 1270     2         3       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "booking %>%\n",
    "    filter(guest_id %in% c(1185, 1270)) %>%\n",
    "    group_by(guest_id) %>%\n",
    "    summarise(n_booking=n(), n_nights=sum(nights))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.0.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
